package com.demo.util;

import com.demo.dao.StudentMapper;
import com.demo.entity.Major;
import com.demo.entity.Student;
import com.demo.entity.Teacher;
import org.apache.http.HttpResponse;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;


import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Logger;

/**
 * Created by 67380 on 2016/10/25.
 */
public class ExcelUtil {
    public static List<Student> readStudentXSSFAllRows(XSSFWorkbook xwb) {
        List<Student> studentList=new ArrayList<Student>();
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row;
        DecimalFormat df = new DecimalFormat("0");
        for (int i = sheet.getFirstRowNum()+1; i < sheet.getLastRowNum()+1; i++) {

            try{
                row = sheet.getRow(i);
                Student student=new Student();
                student.setSid(row.getCell(2).toString());
                student.setSticket(df.format(row.getCell(0).getNumericCellValue()));
                student.setSname(row.getCell(1).toString());
                student.setSsex(row.getCell(3).toString());
                student.setSbirthday(row.getCell(4).toString());
                if(row.getCell(5).getCellType()== Cell.CELL_TYPE_NUMERIC){
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    String temp=row.getCell(5).getStringCellValue();
                    student.setSmajorId(temp.substring(0,temp.length()-2));
                }
                else
                    student.setSmajorId(row.getCell(5).toString());

                student.setStel(row.getCell(6).toString());
                if(row.getCell(7)!=null)
                    student.setScomment(row.getCell(7).toString());
                student.setSpwd(getpwd(student.getSid()));
                studentList.add(student);
            }catch (NullPointerException e){
                e.printStackTrace();
                break;
            }

        }
        return studentList;
    }
    public static List<Major> readMajorXSSFAllRows(XSSFWorkbook xwb) {
        List<Major> majorList=new ArrayList<Major>();
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row;
        for (int i = sheet.getFirstRowNum()+1; i < sheet.getLastRowNum()+1; i++) {

            try{
                row = sheet.getRow(i);
                Major major=new Major();
                if(row.getCell(0).getCellType()== Cell.CELL_TYPE_NUMERIC){
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    String temp=row.getCell(0).getStringCellValue();

                    major.setCmajorId(temp.substring(0,temp.length()-2));
                }
                else
                    major.setCmajorId(row.getCell(0).toString());
                major.setCname(row.getCell(1).toString());
                major.setCollege(row.getCell(2).toString());
                major.setCmax(6);
                majorList.add(major);
            }catch (NullPointerException e){
                e.printStackTrace();
                break;
            }

        }
        return majorList;
    }
    public static List<Teacher> readTeacherXSSFAllRows(XSSFWorkbook xwb) {
        List<Teacher> teacherList=new ArrayList<Teacher>();
        XSSFSheet sheet = xwb.getSheetAt(0);
        XSSFRow row;
        DecimalFormat df = new DecimalFormat("0");
        for (int i = sheet.getFirstRowNum()+1; i < sheet.getLastRowNum()+1; i++) {

            try{
                row = sheet.getRow(i);
                Teacher teacher=new Teacher();
                teacher.setTid(df.format(row.getCell(0).getNumericCellValue()));
               teacher.setTname(row.getCell(1).toString());
                if(row.getCell(2).getCellType()== Cell.CELL_TYPE_NUMERIC){
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    String temp=row.getCell(2).getStringCellValue();
                    teacher.setTmajorId(temp.substring(0,temp.length()-2));
                }
                else
                    teacher.setTmajorId(row.getCell(2).toString());
                teacher.setTintroduction(row.getCell(3).toString());
                teacher.setTpwd(Md5.Md5("888888"));
                teacherList.add(teacher);

            }catch (NullPointerException e){
                e.printStackTrace();
                break;
            }

        }
        return teacherList;
    }
    private static String getpwd(String card){
        String a=card.substring(card.length()-6,card.length());
        return Md5.Md5(a);
    }
    public  final static void exportStuExcel(String fileName, String[] Title, List<Student> listContent,
                                             String[] Title1, List<Teacher> teachers,
                                             HttpServletResponse response) {

        try {

            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(fileName, "UTF-8"));
            //定义输出流，以便打开保存对话框_______________________end

            /** **********创建工作簿************ */

            XSSFWorkbook workBook = new XSSFWorkbook();

            /** **********创建工作表************ */

            XSSFSheet sheet = workBook.createSheet();// 创建一个工作薄对象


            XSSFRow row = sheet.createRow(0);// 创建一个行对象

            row.setHeightInPoints(23);// 设置行高23像素

            XSSFCellStyle style = workBook.createCellStyle();// 创建样式对象

            // 设置字体

            XSSFFont font = workBook.createFont();// 创建字体对象

            font.setFontHeightInPoints((short) 15);// 设置字体大小

            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

            font.setFontName("黑体");// 设置为黑体字

            style.setFont(font);// 将字体加入到样式对象

            // 设置对齐方式

            style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

            // 设置边框

            style.setBorderTop(HSSFCellStyle.BORDER_THICK);// 顶部边框粗线

            style.setTopBorderColor(HSSFColor.RED.index);// 设置为红色

            style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);// 底部边框双线

            style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);// 左边边框

            style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);// 右边边框

            /** ***************以下是EXCEL开头大标题，暂时省略********************* */
            //sheet.mergeCells(0, 0, colWidth, 0);
            //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
            /** ***************以下是EXCEL第一行列标题********************* */
            for (int i = 0; i < Title.length; i++) {
                row.createCell((short) i).setCellValue(Title[i]);
            }
            /** ***************以下是EXCEL正文数据********************* */

            for (int i = 0; i < listContent.size(); i++)
            {
                row = sheet.createRow((int) i + 1);
                Student stu =  listContent.get(i);
                // 第四步，创建单元格，并设置值

                row.createCell((short) 0).setCellValue(stu.getSticket());
                row.createCell((short) 1).setCellValue(stu.getSname());
                row.createCell((short) 2).setCellValue(stu.getMajor().getCname());
                row.createCell((short)3).setCellValue(stu.getSid());
                if(stu.getTeachers().size()>0)
                row.createCell((short)4).setCellValue(stu.getTeachers().get(0).getTname());
                else
                    row.createCell((short)4).setCellValue("未分配");
            }


            sheet = workBook.createSheet();// 创建一个工作薄对象


             row = sheet.createRow(0);// 创建一个行对象

            row.setHeightInPoints(23);// 设置行高23像素

             style = workBook.createCellStyle();// 创建样式对象

            // 设置字体

             font = workBook.createFont();// 创建字体对象

            font.setFontHeightInPoints((short) 15);// 设置字体大小

            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体

            font.setFontName("黑体");// 设置为黑体字

            style.setFont(font);// 将字体加入到样式对象

            // 设置对齐方式

            style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中

            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

            // 设置边框

            style.setBorderTop(HSSFCellStyle.BORDER_THICK);// 顶部边框粗线

            style.setTopBorderColor(HSSFColor.RED.index);// 设置为红色

            style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);// 底部边框双线

            style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);// 左边边框

            style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);// 右边边框

            /** ***************以下是EXCEL开头大标题，暂时省略********************* */
            //sheet.mergeCells(0, 0, colWidth, 0);
            //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
            /** ***************以下是EXCEL第一行列标题********************* */
            for (int i = 0; i < Title1.length; i++) {
                row.createCell((short) i).setCellValue(Title1[i]);
            }
            /** ***************以下是EXCEL正文数据********************* */

            for (int i = 0; i < teachers.size(); i++)
            {
                row = sheet.createRow((int) i + 1);
                Teacher tea =  teachers.get(i);
                // 第四步，创建单元格，并设置值

                row.createCell((short) 0).setCellValue(tea.getTid());
                row.createCell((short) 1).setCellValue(tea.getTname());
                row.createCell((short) 2).setCellValue(tea.getMajor().getCname());
                row.createCell((short)3).setCellValue(tea.getMajor().getCollege());
                for(int j=0;j<tea.getStudentList().size();j++){
                    Student stu=tea.getStudentList().get(j);
                    row.createCell((short)4+j).setCellValue(stu.getSname());
                }
            }
            /** **********将以上缓存中的内容写到EXCEL文件中******** */
            OutputStream outputStream = response.getOutputStream();
            workBook.write(outputStream);
            /** *********关闭文件************* */
            outputStream.close();

        } catch (Exception e) {

            e.printStackTrace();
        }
    }
}
